# 2  设计一个留言版的表（ID，留言内容，留言人，留言时间，是否删除）（表名，和字段名自己设计成英文：注意，不要用中文，用中文的直接0分）；
#    使用PyMySQL 驱动模块，实现对这个表的增加，删除，修改，查询；数据库操作需要加入异常处理逻辑；

import datetime
import time

import pymysql
#打开数据库连接

class Oper_Sql:
    insert = 'insert into message(id,message,message_name,message_time,delect_YN) values(%s,%s,%s,%s,%s)'

conn = pymysql.connect(host="localhost", user="xulili", password="456281937", database="message",charset="utf8mb4")
#获取游标
cursor=conn.cursor()
print(cursor)

def insert():
    insert_count = int(input('输入您要插入的总条数: '))
    try:
        while insert_count > 0:
            id =  int(time.time())
            message = input('输入留言内容: ')
            message_name = input('亲输入留言姓名: ')
            message_time = datetime.datetime.now()
            delect_YN = 'N'

            with conn.cursor() as cursor:
                cursor.execute(query=Oper_Sql.insert, args=[id,message,message_name,message_time,delect_YN])
                for row in cursor.fetchall():
                    id = row[0]
                    message = row[1]
                    message_name = row[2]
                    message_time = row[3]
                    delect_YN = row[4]

                print('id: %s 留言内容: %s 留言者姓名: %s 留言时间: %s 是否删除: %s ' % (id,message,message_name,message_time,delect_YN))
                insert_count -= 1
                conn.commit()
    except Exception as err:
        print(err)
    finally:
        print("")

def delect():
  try:
    cursor.execute("select * from message;")
    print('删除前的数据为：')
    for res in cursor.fetchall():
        print(res)

    print('*' * 40)
    # 删除1条数据
    i = input("你想删除的序号")
    cursor.execute("delete from message where id=%s"% i)

    # 删除后查询所有数据
    cursor.execute("select * from message;")
    print('删除后的数据为：')
    for res in cursor.fetchall():
        print(res)
    cursor.close()
    conn.commit()
    print('sql执行成功')
  except:
      print("Error")

def find():
    sql = "SELECT * FROM message \
           WHERE id = %s" % input("查询留言者id：")
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            id = row[0]
            message = row[1]
            message_name = row[2]
            message_time = row[3]
            delect_YN = row[4]

        print('id: %s 留言内容: %s 留言者姓名: %s 留言时间: %s 是否删除: %s ' % (id, message, message_name, message_time, delect_YN))
    except:
        print("Error: unable to fetch data")

def update():
    id = input("修改的留言者id")
    message = input("修改内容")
    try:
        cursor.execute('update message set message = "%s" where id = %s' % (message,id))
        conn.commit()
    except Exception as e:
        print("修改数据库失败",e)
    cursor.close()



if __name__ == '__main__':
  while True:
      print('1. 增加信息：')
      print('2. 删除信息')
      print('3. 查询信息')
      print('4. 更新数据')
      print('0.退出')
      index_sql = input('请输入操作指令:')
      if index_sql == '1':
          insert()
      elif index_sql == '2':
          delect()
      elif index_sql == '3':
          find()
      elif index_sql == '4':
          update()
      elif index_sql == '0':
          break